package com.suitsoft.ut.dao.thesis;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springside.modules.orm.Page;
import org.springside.modules.orm.hibernate.HibernateDao;
import org.springside.modules.utils.reflection.ConvertUtils;

import com.google.common.collect.Lists;
import com.suitsoft.ut.commons.Constants;
import com.suitsoft.ut.commons.UserHolder;
import com.suitsoft.ut.dao.account.UserDao;
import com.suitsoft.ut.dao.base.CollegeDao;
import com.suitsoft.ut.dao.base.TeacherDao;
import com.suitsoft.ut.entity.account.Authority;
import com.suitsoft.ut.entity.account.Role;
import com.suitsoft.ut.entity.account.User;
import com.suitsoft.ut.entity.base.Student;
import com.suitsoft.ut.entity.base.Teacher;
import com.suitsoft.ut.entity.base.Team;
import com.suitsoft.ut.entity.thesis.Thesis;
import com.suitsoft.ut.util.StringUtils;

/**
 * 题 DAO层类
 *@author Suineg
 */
@Repository
public class ThesisDao extends HibernateDao<Thesis,Long>{
	/** 日志输出对象 */
	protected final Logger logger = LoggerFactory.getLogger(getClass());
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private UserDao userDao;
	@Autowired
	private CollegeDao collegeDao;
	@Autowired
	private TeacherDao teacherDao;
	
	public Page<Map<String,Object>>findThesis(Page<Map<String,Object>> page,String teacher,String group,Long status,Long flag){
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT T.ID,T.TITILE,T.TEACHER_POST, T.STUDENT_NO,T.STUDENT_NAME, T.TEACHER_NAME,T.SET_THE_TIME, TF.THESIS_STATUS ");
		sb.append(" FROM LW_THESIS T, LW_COLLEGE C, BD_TEACHER TH, LW_THESIS_FILE TF WHERE T.ID = TF.THESIS_ID AND T.COLLEGE_ID = C.ID AND T.TEACHER_NO = TH.TEACHER_NO AND T.TEACHER_NO IN ");
		sb.append("   (SELECT TH.TEACHER_NO FROM BD_TEACHER TH, LW_GROUP G, LW_TEACHER_GROUP TG WHERE TH.ID = TG.TEACHER_ID ");
		sb.append(" AND TG.GROUP_ID = G.ID  ");
		if(!StringUtils.isEmpty(teacher)){
			sb.append(" AND TH.TEACHER_NAME = '"+teacher+"' ");
		}
		
		if(!StringUtils.isEmpty(group)){
			sb.append(" AND G.GROUP_NAME = '"+group+"'");
		}
	//	if(status != null && status != 0l){
	//		sb.append(" AND TF.thesis_status = " + status);
	//	}
		sb.append(" AND TF.THESIS_FLAG =" + flag);
		sb.append(" AND abs(TF.thesis_status) = " + status);
		sb.append(" and g.id in (select g.id from lw_group g, bd_teacher t,LW_TEACHER_GROUP TG where  TG.GROUP_ID = G.ID   and T.ID = TG.TEACHER_ID and t.id = "+teacherDao.findByUser(UserHolder.getLoginUser().getId()).getId()+") )");
		sb.append(" order by SET_THE_TIME");
		
		
		logger.info(sb.toString());
		return jdbcTemplate.queryForPage(page, sb.toString());
	}
	
	//选题查询
	public Page<Thesis> findBySelect(Page<Thesis> page,Long flag,Long status,String teacherName,String titile,Teacher teacher){
		String hql = "from Thesis t where t.thesisFile.thesisFlag = " + flag +" and t.thesisFile.thesisStatus ="+status;
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%'";
		}
		if(!StringUtils.isEmpty(titile)){
			hql += " and t.titile like '%"+titile+"%'";
		}
		hql += getSql();
		return findPage(page, hql);
		
	}
	
	//选题查询
	public Page<Thesis> findBySelectTeacher(Page<Thesis> page,Long flag,List<Long> status,String teacherName,String titile,Teacher teacher){
		String hql = "from Thesis t where t.thesisFile.thesisFlag = " + flag +" and t.thesisFile.thesisStatus in(:stauts)";
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%'";
		}
		if(!StringUtils.isEmpty(titile)){
			hql += " and t.titile like '%"+titile+"%'";
		}
		Map<String,Object> m = null;
		m = new HashMap<String,Object>();
		m.put("stauts", status);
		hql += getSql();	
		return findPage(page, hql,m);
		
	}
	
	//选题查询
	/**
	 * 学生选择查询
	 */
	public Page<Thesis> findBySelect(Page<Thesis> page,Long flag,Long status,String teacherName,String titile,Student student){
		String hql = "from Thesis t where t.thesisFile.thesisFlag = " + flag +" and t.thesisFile.thesisStatus ="+status;
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%'";
		}
		if(!StringUtils.isEmpty(titile)){
			hql += " and t.titile like '%"+titile+"%'";
		}
		hql += " and t.discipline.id  = " +student.getDiscipline().getId();
		if(student.getThesis() != null){
			hql += " and 1 =2";
		}
		hql += getSql();
		return findPage(page, hql);
	}
	
	public List<Thesis> findExaminationBy(Page<Thesis> page,Long flag,Long status,String teacherName,String titile,Long isStudentExamination,Long isTeacherExamination,Teacher teacher){
		String hql = "from Thesis t where t.thesisFile.thesisFlag = " + flag +" and t.thesisFile.thesisStatus ="+status;
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%'";
		}
		if(!StringUtils.isEmpty(titile)){
			hql += " and t.titile like '%"+titile+"%'";
		}
		if(isStudentExamination != null && isStudentExamination != 0L){
			hql += "and t.thesisFile.isExaminationStudentOk = " + isStudentExamination;
		}
		if(isTeacherExamination != null && isTeacherExamination != 0L){
			hql += "and t.thesisFile.isExaminationTeacherOk = " + isTeacherExamination;
		}
		hql += getSql();
		return find(hql);
	}
	
	public List<Thesis> findExaminationBy(Page<Thesis> page,List<Long> flag,List<Long> status,String teacherName,String titile,Long isStudentExamination,Long isTeacherExamination){
		String hql = "from Thesis t where t.thesisFile.thesisFlag in(:flag) and t.thesisFile.thesisStatus in(:status)";
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%'";
		}
		if(!StringUtils.isEmpty(titile)){
			hql += " and t.titile like '%"+titile+"%'";
		}
		if(isStudentExamination != null && isStudentExamination != 0L){
			hql += "and t.thesisFile.isExaminationStudentOk = " + isStudentExamination;
		}
		if(isTeacherExamination != null && isTeacherExamination != 0L){
			hql += "and t.thesisFile.isExaminationTeacherOk = " + isTeacherExamination;
		}
		hql += getSql();
		Map<String,Object> m = new HashMap<String,Object>();
		m.put("status", status);
		m.put("flag", flag);
		return find(hql,m);
	}
	/**
	 * 搜索已被选中的论文 
	 * @return
	 */
	public List<Thesis> findSelcted(){
		String hql = "from Thesis t where t.thesisFile.isSelect = 1";
		hql += getSql();
		return find(hql);
	}
	
	public List<Thesis> findSelectedForMidlle(Teacher teacher){
		String hql = "from Thesis t where t.thesisFile.isSelect = 1 and t.thesisFile.thesisFlag = " + Constants.THESIS_FLAG_MIDLLE;
		hql += getSql();
		return find(hql);
	}
	
	public List<Thesis> findSelectedForMidlle(String collegeCode){
		String hql = "from Thesis t where t.thesisFile.isSelect = 1 and t.thesisFile.thesisFlag =  " + Constants.THESIS_FLAG_MIDLLE;
		hql += getSql();
		return find(hql);
	}
	
	//选题查询
	public Page<Thesis> findBySelect(Page<Thesis> page,Long flag,List<Long> status,String teacherName,String titile){
		String hql = "from Thesis t where t.thesisFile.thesisFlag = " + flag +" and t.thesisFile.thesisStatus in (:stauts)";
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%'";
		}
		if(!StringUtils.isEmpty(titile)){
			hql += " and t.titile like '%"+titile+"%'";
		}
		hql += getSql();
		Map<String,Object> m = new HashMap<String,Object>();
		m.put("stauts", status);
		return findPage(page, hql,m);
	}
	
	//选题查询
	public Page<Thesis> findBySelect(Page<Thesis> page,Long flag,List<Long> status,String teacherName,String titile,Student student){
		String hql = "from Thesis t where t.thesisFile.thesisFlag = " + flag +" and t.thesisFile.thesisStatus in (:stauts)";
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%'";
		}
		if(!StringUtils.isEmpty(titile)){
			hql += " and t.titile like '%"+titile+"%'";
		}
		hql += getSql();
		hql += " and t.studentNo = " + student.getStudentNo();
		Map<String,Object> m = new HashMap<String,Object>();
		m.put("stauts", status);
		return findPage(page, hql,m);
	}
	
	//选题查询
	public Page<Thesis> findBySelect(Page<Thesis> page,Long flag,List<Long> status,String teacherName,String titile,Teacher teacher){
		String hql = "from Thesis t where t.thesisFile.thesisFlag = " + flag +" and t.thesisFile.thesisStatus in (:stauts)";
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%'";
		}
		if(!StringUtils.isEmpty(titile)){
			hql += " and t.titile like '%"+titile+"%'";
		}
		hql += getSql();
		Map<String,Object> m = new HashMap<String,Object>();
		m.put("stauts", status);
		return findPage(page, hql,m);
	}
	
	//选题查询
	public List<Thesis> findAllBySelect(Page<Thesis> page,Long flag,List<Long> status,String teacherName,String titile){
		String hql = "from Thesis t where t.thesisFile.thesisFlag = " + flag +" and t.thesisFile.thesisStatus in (:stauts)";
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%'";
		}
		if(!StringUtils.isEmpty(titile)){
			hql += " and t.titile like '%"+titile+"%'";
		}
		hql += getSql();
		Map<String,Object> m = new HashMap<String,Object>();
		m.put("stauts", status);
		return find( hql,m);
	}
	
	//选题查询
	public Page<Thesis> findBySelect(Page<Thesis> page,List<Long> flag,List<Long> status,String teacherName,String titile){
		String hql = "from Thesis t where t.thesisFile.thesisFlag in (:flags) and t.thesisFile.thesisStatus in (:stauts)  ";
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%'";
		}
		if(!StringUtils.isEmpty(titile)){
			hql += " and t.titile like '%"+titile+"%'";
		}
		hql += getSql();
		Map<String,Object> m = new HashMap<String,Object>();
		m.put("stauts", status);
		m.put("flags", flag);
		return findPage(page, hql,m);
	}

	public List<Thesis> findByNoStudent(){
		String hql = "from Thesis t where t.studentNo is null";
		return find(hql);
	}
	
	/**
	 * 已经确定学生的题目
	 * @param filters
	 * @return
	 */
	public Page<Thesis>findByConfirmStudent(Page<Thesis> page,String teacherName,String studentName,String titile,String collegeCode){
		String hql = "from Thesis t where t.studentNo is not null";
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%' ";
		}
		if(!StringUtils.isEmpty(studentName)){
			hql += " and t.studentName like '%"+studentName+"%'";
		}
		if(!StringUtils.isEmpty(titile)){
			hql += " and t.titile like '%"+titile+"%'";
		}
		hql += getSql();
		hql += " and t.thesisFile.thesisFlag = " + Constants.THESIS_FLAG_XUANTI;
		hql += " and t.thesisFile.thesisStatus = " + Constants.THSIS_STAUTS_SELECT_AUDIT;
		
		return findPage(page, hql);
	}
	
	public Page<Thesis> findOld(Page<Thesis> page,String year,String teacherName,String stduentName,String studentNo,Long flag,Long stauts){
		String hql = "from Thesis t ";
		if(!StringUtils.isEmpty(year)){
			hql += " and t.thesisYear = " + year;
		}
		if(!StringUtils.isEmpty(teacherName)){
			hql += " and t.teacherName like '%"+teacherName+"%'";
		}
		if(!StringUtils.isEmpty(stduentName)){
			hql += " and t.studentName like '%"+stduentName+"%'";
		}
		if(!StringUtils.isEmpty(studentNo)){
			hql += " and t.studentNo like '%"+studentNo+"%'";
		}
		hql += getSql();
		hql += "t.thesisFile.thesisStatus = " + stauts;
		hql += "t.thesisFile.thesisFlag = " + flag;
		return findPage(page, hql);
	}
	
	/**
	 * 数据权限部分
	 * @return
	 */
	public String getSql(){
		String sql = "";
		User user = userDao.get(UserHolder.getLoginUser().getId());
		String collegeCode = UserHolder.getLoginUser().getCollegeCode();
		Teacher teacher = user.getTeacher();
		if(teacher == null){
			return "";
		}
		
		for(Role r : user.getRoleList()){
			//校管理 能看到所有
			if(r.getAuthNames().indexOf(Constants.CN_SCHOOL_ADMIN) != -1){
				return " and 1 = 1 ";
			}
			for(Authority a : r.getAuthorityList()){
				//学院管理 能看到 他所在学院的
				if(a.getName().equals(Constants.CN_COLLEGE_ADMIN)){
					sql += " and t.college.id in (" + collegeCode +")";
					return sql;
				}
			}
		}
		String teacherNos = null;
		
		List<Teacher> list = Lists.newArrayList();
		if(teacher.getIsMaster() != null && teacher.getIsMaster() == 1){
			for(Team t : teacher.getTeamList()){
				list.addAll(t.getTeacherList());
			}
			if(!list.isEmpty()){
				teacherNos = ConvertUtils.convertElementPropertyToString(list, "teacherNo", ",");
				teacherNos = "'" + teacherNos.replace(",","','") + "'";
			}
		}else{
			teacherNos = "'" + teacher.getTeacherNo() + "'";
		}

		sql += " and t.teacherNo in (" + teacherNos + ")";
		return sql;
	}
}

